package fu.abc.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import fu.abc.model.Account;


public class ManageDAO {
	
	Connection connect = null;
	PreparedStatement pre = null;
	ResultSet result = null;
	
	
//	login
	
	public boolean login(String account, String password) throws SQLException{
			
			try {
				connect = DAOconnection.getConnection();
				if(connect!=null){
					String query = "SELECT account,password from Account WHERE account=? AND password=?";
					pre = connect.prepareStatement(query);
					pre.setString(1, account);
					pre.setString(2, password);
					result = pre.executeQuery();
					boolean check = result.next();
					if(check){
						return true;
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(connect!=null){
					connect.close();
				}
			}
			return false;
		}
	
//	list account
	
	public List<Account> listAccount(){
				List<Account> list = new ArrayList<Account>();
		try {
			connect = DAOconnection.getConnection();
			String query = "SELECT idAcc,username,role,account FROM Account ";
			Statement stm = connect.createStatement();
			result = stm.executeQuery(query);
			while(result.next()){
				Account acc = new Account();
				acc.setIdAcc(result.getInt("idAcc"));
				acc.setUsername(result.getString("username"));
				acc.setRole(result.getString("role"));
				acc.setAccount(result.getString("account"));
				list.add(acc);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(connect!=null){
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return list;
		
	}
	
//	view account 
	
public Account getAccountInfor(int id) throws SQLException{
		
		Account acc = new Account();
		try {
			connect = DAOconnection.getConnection();
			if(connect!=null){
				
				String query = "SELECT * FROM Account WHERE idAcc='"+id+"'";
				pre = connect.prepareStatement(query);
				result = pre.executeQuery();
				
				while(result.next()){
					acc.setIdAcc(result.getInt("idAcc"));
					acc.setUsername(result.getString("username"));
					acc.setAddress(result.getString("address"));
					acc.setAccount(result.getString("account"));
					acc.setPassword(result.getString("password"));
					acc.setRepass(result.getString("repassword"));
					acc.setDob(result.getString("DOB"));
					acc.setCmt(result.getInt("cmt"));
					acc.setEmail(result.getString("email"));
					acc.setPhone(result.getInt("phone"));
					acc.setRole(result.getString("role"));
					
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(connect!=null){
				connect.close();
			}
		}
		
		return acc;		
	}

//	delete account

	public void delete(int id) throws SQLException{
		try {
			connect = DAOconnection.getConnection();
			
			Statement stm = connect.createStatement();
			stm.execute("DELETE FROM Account WHERE idAcc = "+id+" ");
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(connect!=null){
				connect.close();
			}
		}
	}
	
//	edit account
	
public void updateAccount(Account account){
		
		try {
			connect = DAOconnection.getConnection();
			if(connect!=null){
				String query = "UPDATE Account SET username=?,DOB=?,address=?,cmt=?,email=?,phone=?,password=?,repassword=?,role=? WHERE idAcc=?";
				pre = connect.prepareStatement(query);
				pre.setString(1, account.getUsername());
				pre.setString(2, account.getDob());
				pre.setString(3, account.getAddress());
				pre.setInt(4, account.getCmt());
				pre.setString(5, account.getEmail());
				pre.setInt(6, account.getPhone());
				pre.setString(7, account.getPassword());
				pre.setString(8, account.getRepass());
				pre.setString(9, account.getRole());
				pre.setInt(10, account.getIdAcc());
				
				pre.executeUpdate();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if(connect!=null){
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}

//	create account

	public boolean createAccount(Account account) throws SQLException{
		connect = DAOconnection.getConnection();
		if(connect!=null){
			try {
				
				String query = "INSERT INTO Account(username,address,account,password,repassword,DOB,cmt,email,phone,role"
						+ ") VALUES(?,?,?,?,?,?,?,?,?,?)";
				
				pre = connect.prepareStatement(query);
				
				pre.setString(1, account.getUsername());
				pre.setString(2, account.getAddress());
				pre.setString(3, account.getAccount());
				pre.setString(4, account.getPassword());
				pre.setString(5, account.getRepass());
				pre.setString(6, account.getDob());
				pre.setInt(7, account.getCmt());
				pre.setString(8, account.getEmail());
				pre.setInt(9, account.getPhone());
				pre.setString(10, account.getRole());
				
				pre.execute();
				return true;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}finally{
				if(connect!=null){
					connect.close();
				}
			}
		} else return false;
	}
}
